﻿CREATE PROCEDURE [dbo].[SPRolledOverPTOHours]  (
	-- Add the parameters for the stored procedure here
	@userID int, @date datetime)
AS
BEGIN
   -- Insert statements for procedure here
	declare @daysEmployed int
	declare @yearBegin datetime;
	declare @yearEnd datetime;
	declare @yearPTO int;
	declare @AllowedPTO float;
	declare @daysInYear int;
	declare @expectedHours int;
	declare @PTOHours int;
	declare @RollOverHours float;
	
	
	set @RollOverHours = 0;
	set @yearBegin = dbo.MDate((DATEPART(yy, @date)-1),1,1);
	set @yearEnd   = dbo.MDate((DATEPART(yy, @date)-1),12,31);
	
	-- 2012, 2016 and 2020 are leap years... need to fix
	set @daysEmployed = dbo.GetDaysInYear(DATEPART(yy, @date)-1);
	set @daysInYear = dbo.GetDaysInYear(DATEPART(yy, @date)-1);
	

	
	if (select StartDate from Users where UserID = @userID) > @yearBegin
	begin
		set @daysEmployed = (select DATEDIFF(day, StartDate, @yearEnd) AS NumberOfDays from Users where UserID = @userID)
	end
	
print @userID
print @daysEmployed
	    
	set @expectedHours = (select ExpectedHours from Users where UserID = @userID) 	
	set @AllowedPTO = coalesce((CAST(@daysEmployed as float) / @daysInYear) * (CAST(@expectedHours as float) / 40) * 120,0);
	
print @expectedHours
print @AllowedPTO
	
	select @PTOHours = coalesce(sum(duration),0) from EntryLog
		where UserID = @userID and EntryDate  between @yearBegin and @yearEnd and CategoryID in (7,13) 
	set @RollOverHours = (@AllowedPTO - CAST(@PTOHours as float));
	
print @PTOHours
print @RollOverHours

	if @RollOverHours > 40
	begin
		set @RollOverHours = 40;
	end
	else
	if @RollOverHours < 0
	begin
		set @RollOverHours = 0;
	end
		
	RETURN @RollOverHours;
END